
package hk.ces.oxfam.patch.transaction;

import hk.ces.oxfam.patch.TransactionPatch;
import hk.ces.oxfam.patch.TransactionStrategy;
import hk.ces.oxfam.patch.TransactionStrategyType;
import hk.ces.oxfam.patch.sql.ExcuteUtil;
import hk.ces.oxfam.patch.db.SQLUtil;
import hk.ces.oxfam.patch.db.result.BeanResult;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.obj.AssignmentItem;
import hk.ces.oxfam.patch.obj.BatchItem;
import hk.ces.oxfam.patch.obj.BeanPacker;
import hk.ces.oxfam.patch.sql.PreparedSQL;
import hk.ces.oxfam.patch.xml.SQLResultXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
@TransactionStrategy(TransactionStrategyType.COMMIT_IN_EACH_STATEMENT)
public class AssignmentPatch implements TransactionPatch
{
  private static final String ONLINE_PAYMENT = "ONLINE_PAYMENT";
  private static final String ASSIGNMENT_DONATION_TYPE_TEAM = "TEAM";
  private static final String TXSTATE_CONFIRM = "CONFIRM";
  private static final String ALLOCATION_DONATION_TYPE_TEAM = "TEAM";
  private static final String ALLOCATION_DONATION_TYPE_MEMBER = "MEMBER";
  
  public void run(Connection connection) throws SQLException
  {
    SQLResultXMLFactory factory = new SQLResultXMLFactory("AllOnlineDonationWithoutAssignment");
    
    List<BatchItem> batchItems = this.getAllOnlineDonationWithoutAssignment(connection, factory);
    Logger.getLogger(this.getClass()).debug("Total Batch Item [count=" + batchItems.size() + "]");

    int index = 0;
    for (BatchItem batchItem : batchItems)
    {
      List<AssignmentItem> assignmentItems = this.getAssignmentItemFromBatch(connection, batchItem);
      Logger.getLogger(this.getClass()).debug(index++ + ". BatchItem[id=" + batchItem.getId() + "] have Assignment Item [count=" + assignmentItems.size() + "]");

      List<Long> newAssignmentIds = this.insertAssignments(connection, assignmentItems);
      this.insertRelationships(connection, batchItem, newAssignmentIds);

      Logger.getLogger(this.getClass()).debug("Batch[id=" + batchItem.getId() + ", batchno=" + batchItem.getBatchNo() + "] patch Assignment success!");
      Logger.getLogger(this.getClass()).debug("----------------------------------------------------------------");
    }
  }

  private void insertRelationships(Connection connection, BatchItem batchItem, List<Long> newAssignmentIds) throws SQLException
  {
    for (Long newAssignmentId : newAssignmentIds)
    {
      while (true)
      {
        String sql = "insert into batch_otwteamtotalamount values(?, ?)";
        Object[] params = new Object[]{batchItem.getId(), newAssignmentId};

        int result = ExcuteUtil.excute(connection, new PreparedSQL(sql, params));
        if (result == 1)
        {
          Logger.getLogger(this.getClass()).debug("Relationshop between Batch[id=" + batchItem.getId() + "] and New Assignment [id=" + newAssignmentId + "] have built");
          break;
        }
      }
    }
  }

  private List<Long> insertAssignments(Connection connection, List<AssignmentItem> assignmentItems) throws SQLException
  {
    List<Long> newAssignmentIds = new ArrayList<Long>(assignmentItems.size());
    for (AssignmentItem item : assignmentItems)
    {
      while (true)
      {
        String sql = "select max(id) from otwteamtotalamount";
        Object[] params = new Object[]{};
        Long maxAssignmentId = SQLUtil.single(connection, new hk.ces.oxfam.patch.db.sql.PreparedSQL(sql, params), Long.class);

        sql = "insert into otwteamtotalamount (ID, CREATEDBY, CREATEDDATE, LASTMODIFIEDBY, LASTMODIFIEDDATE, AMOUNT, DISABLED, DONATIONTYPE, TEAMNO, OPTCOUNT, LUCKYDRAWLOCK, LUCKYDRAWMARK) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        long newAssignmentId = maxAssignmentId + 1;
        Date now = new Date();
        params = new Object[]{newAssignmentId, "LUCKY_DRAW_DBPATCH", new Timestamp(now.getTime()), "LUCKY_DRAW_DBPATCH", new Timestamp(now.getTime()), item.getAmount(), false, ASSIGNMENT_DONATION_TYPE_TEAM, item.getTeamNo(), 1, false, false};

        try
        {
          int result = ExcuteUtil.excute(connection, new PreparedSQL(sql, params));
          if (result == 1)
          {
            Logger.getLogger(this.getClass()).debug("New Assignment have inserted[id=" + newAssignmentId + ", teamno=" + item.getTeamNo() + ", amount=" + item.getAmount() + "]");
            newAssignmentIds.add(newAssignmentId);
            break;
          }
        }
        catch (SQLException exception)
        {
          Logger.getLogger(this.getClass()).error("--" + exception.getMessage());
        }
      }
    }

    return newAssignmentIds;
  }
  
  private List<AssignmentItem> getAssignmentItemFromBatch(Connection connection, BatchItem batchItem) throws SQLException
  {
    String groupTxAmountByBatchSql = "select sponsoredteamid, sum(hkamount) as amountsun from transaction where disabled = ? and transactionstate = ? and donationtype in (?, ?) and id in (select trasnactions_id from batch_transaction where batch_id = ?) group by sponsoredteamid";
    String groupTeamNoAndTxAmountByBatchSql = "select otwteam.teamno, groupTx.amountsun from (" + groupTxAmountByBatchSql + ") as groupTx inner join otwteam on otwteam.id = groupTx.sponsoredteamid";
    Object[] params = new Object[]{false, TXSTATE_CONFIRM, ALLOCATION_DONATION_TYPE_TEAM, ALLOCATION_DONATION_TYPE_MEMBER, batchItem.getId()};

    BeanResult<AssignmentItem> beanResult = new BeanResult<AssignmentItem>(SQLUtil.query(connection, new hk.ces.oxfam.patch.db.sql.PreparedSQL(groupTeamNoAndTxAmountByBatchSql, params)))
    {
      @Override
      public BeanPacker<AssignmentItem> packer(final SQLResult result)
      {
        return new BeanPacker<AssignmentItem>()
        {
          public AssignmentItem pack(Object[] objects)
          {
            AssignmentItem item = new AssignmentItem();
            item.setTeamNo((String) objects[0]);
            item.setAmount((Double) objects[1]);

            return item;
          }
        };
      }
    };

    return beanResult.getPackedResult();
  }

  private List<BatchItem> getAllOnlineDonationWithoutAssignment(Connection connection, SQLResultXMLFactory factory) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String onlineBatchIdSql = "select id from batch where disabled = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    String batchIdsWithAssignment = "select distinct batch_id from batch_otwteamtotalamount";
    String batchIdsWithoutAssignment = "select id, batchno from batch where disabled = ? and id in ((" + onlineBatchIdSql + ") except (" + batchIdsWithAssignment + "))";
    Object[] params = new Object[]
    {
      false,
      false,
      false,
      ONLINE_PAYMENT
    };

    BeanResult<BatchItem> beanResult = new BeanResult<BatchItem>(SQLUtil.query(connection, new hk.ces.oxfam.patch.db.sql.PreparedSQL(batchIdsWithoutAssignment, params)))
    {
      @Override
      public BeanPacker<BatchItem> packer(final SQLResult result)
      {
        return new BeanPacker<BatchItem>()
        {
          public BatchItem pack(Object[] objects)
          {
            BatchItem item = new BatchItem();
            item.setId((Long) objects[0]);
            item.setBatchNo((String) objects[1]);

            return item;
          }
        };
      }
    };
    
    factory.setXMLItems(new SQLResultXML(beanResult));
    factory.writeToFile();

    return beanResult.getPackedResult();
  }
}
